﻿using Common.Framework.Data;
using Common.Framework.Extensions;
using Common.Framework;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace DBUtil.Builders
{
    internal class EntityAlias
    {
        public EntityInfo EntityInfo;
        public string Alias { get; set; }
    }
    internal class FromJoin
    {
        public LambdaExpression Expression { get; set; }
        public EnumJoinType Type { get; set; }
    }
    public class SelectBuilderBase : WhereBuilder
    {
        internal List<EntityAlias> EntityAliases;
        internal List<FromJoin> FromJoins;
        internal SelectBuilderBase(DBAccess db) : base(db) { }

        #region AsTable
        public virtual SelectBuilderBase AsTable(Func<string, string> func)
        {
            var newTableName = func?.Invoke(EntityAliases.Last().EntityInfo.TableNameSeg);
            if (newTableName.IsNotNullOrEmptyOrWhiteSpace())
            {
                var entityAlias = EntityAliases.Last();
                entityAlias.EntityInfo.TableNameSeg = newTableName;
            }
            return this;
        }

        public virtual SelectBuilderBase AsTableIf(bool condition, Func<string, string> func)
            => condition ? AsTable(func) : this;
        #endregion

        #region Alias
        public SelectBuilderBase Alias(string alias)
        {
            Ensure.NotNullOrEmptyOrWhiteSpace(alias);
            var entityAlias = EntityAliases.Last();
            entityAlias.Alias = alias;
            return this;
        }
        #endregion

        #region Where
        protected SelectBuilderBase Where(LambdaExpression expression)
        {
            Ensure.NotNull(expression, nameof(expression));
            Filters.Add(expression);
            return this;
        }
        protected SelectBuilderBase WhereIf(bool condition, LambdaExpression expression)
        {
            if (condition)
            {
                Ensure.NotNull(expression, nameof(expression));
                Filters.Add(expression);
            }
            return this;
        }
        #endregion

        #region Distinct
        protected bool isDistinct;
        protected SelectBuilderBase Distinct(bool isDistinct = true)
        {
            this.isDistinct = isDistinct;
            return this;
        }
        #endregion

        #region 排序
        protected List<(object obj, bool desc)> orderBys = [];
        protected SelectBuilderBase OrderBy(LambdaExpression expression)
        {
            orderBys.Add((expression, false));
            return this;
        }

        protected SelectBuilderBase OrderByDesc(LambdaExpression expression)
        {
            orderBys.Add((expression, true));
            return this;
        }

        /// <summary>
        /// 排序,如: select.Order("age desc,id")
        /// </summary>
        protected SelectBuilderBase Order(string orderSeg)
        {
            orderBys.Add((orderSeg, false));
            return this;
        }
        #endregion

        #region 分页
        protected int? pageIndex = null;
        protected int? pageSize = null;
        protected int? limitSize = null;
        protected int? limitStartIndex = null;
        protected SelectBuilderBase Page(int pageIndex, int pageSize)
        {
            Ensure.That(pageIndex >= 1, $"{nameof(pageIndex)}必须从1开始!");
            Ensure.That(pageSize > 0, $"{nameof(pageSize)}必须大于0!");
            this.pageIndex = pageIndex;
            this.pageSize = pageSize;
            this.limitSize = this.limitStartIndex = null;
            return this;
        }

        private SelectBuilderBase limit(int size, int startIndex)
        {
            Ensure.That(startIndex >= 0, $"{nameof(startIndex)}必须大于等于0!");
            Ensure.That(size >= 0, $"{nameof(size)}必须大于等于0!");
            this.limitSize = size;
            this.limitStartIndex = startIndex;
            this.pageIndex = this.pageSize = null;
            return this;
        }
        protected SelectBuilderBase Limit(int size) => this.limit(size, 0);
        protected SelectBuilderBase Limit(int startIndex, int size) => this.limit(size, startIndex);
        #endregion

        #region WithSql
        protected string fromSql = string.Empty;
        protected SelectBuilderBase WithSql(string fromSql)
        {
            this.fromSql = fromSql;
            return this;
        }
        #endregion

        #region AddExtraColumnSelect
        private List<string> extraSelectSegs = new();
        internal virtual SelectBuilderBase AddExtraSelectSeg(string seg)
        {
            extraSelectSegs.Add(seg);
            return this;
        }
        #endregion

        #region ToSql
        internal string ToSqlFirstOrDefault(LambdaExpression expression) => Limit(1).ToSql(EnumSelectToSql.ToList, expression);
        internal string ToSqlFirstOrDefaultDto(List<(string dtoCol, EntityPropertyInfo entPropInfo)> dtoMap) => Limit(1).ToSql(EnumSelectToSql.ToList, dtoMap: dtoMap);
        internal string ToSqlList(LambdaExpression expression) => ToSql(EnumSelectToSql.ToList, expression);
        internal string ToSqlSelectClause(LambdaExpression expression) => ToSql(EnumSelectToSql.ToSelectClause, expression);
        internal string ToSqlListDto(List<(string dtoCol, EntityPropertyInfo entPropInfo)> dtoMap) => ToSql(EnumSelectToSql.ToList, dtoMap: dtoMap);
        internal string ToSqlPage(LambdaExpression expression) => ToSql(EnumSelectToSql.ToPage, expression);
        internal string ToSqlPageDto(List<(string dtoCol, EntityPropertyInfo entPropInfo)> dtoMap) => ToSql(EnumSelectToSql.ToPage, dtoMap: dtoMap);
        public string ToSqlCount() => ToSql(EnumSelectToSql.Count);

        protected string ToSql(EnumSelectToSql enumSelectToSql, LambdaExpression expression = null, Dictionary<ParameterExpression, object> midValues = null, IEnumerable<KeyValuePair<ParameterExpression, string>> aliases = null, List<(string dtoCol, EntityPropertyInfo entPropInfo)> dtoMap = null)
        {
            var sb = new StringBuilder();
            string tmp;
            switch (enumSelectToSql)
            {
                case EnumSelectToSql.Count:
                    sb.Append("select count(1)");
                    break;
                case EnumSelectToSql.ToList:
                case EnumSelectToSql.ToPage:
                    tmp = GetSelectSql(expression, midValues, dtoMap);
                    sb.Append($"select{(isDistinct ? " distinct " : " ")}{tmp}");
                    if (extraSelectSegs.IsNotNullOrEmpty())
                    {
                        var seg = extraSelectSegs.ToStringSeparated(",");
                        sb.Append(',').Append(seg);
                    }
                    break;
                case EnumSelectToSql.ToSelectClause:
                    tmp = GetSelectSql(expression, midValues, dtoMap);
                    return tmp;
                case EnumSelectToSql.Max:
                    tmp = GetSelectSql(expression, midValues);
                    sb.Append($"select max({tmp})");
                    break;
                case EnumSelectToSql.Min:
                    tmp = GetSelectSql(expression, midValues);
                    sb.Append($"select min({tmp})");
                    break;
                case EnumSelectToSql.Sum:
                    tmp = GetSelectSql(expression, midValues);
                    sb.Append($"select sum({tmp})");
                    break;
                case EnumSelectToSql.Avg:
                    tmp = GetSelectSql(expression, midValues);
                    sb.Append($"select avg({tmp})");
                    break;
            }
            var fromSql = GeneFromSql();
            sb.AppendLine().Append(fromSql);

            //过滤
            var whereSql = GeneWhereSql(midValues, aliases);
            if (whereSql.IsNotNullOrEmptyOrWhiteSpace()) sb.AppendLine().Append(whereSql);

            //排序
            var orderSql = GetOrderSql();
            if (orderSql.IsNotNullOrEmptyOrWhiteSpace()) sb.AppendLine().Append(orderSql);

            //分页
            var limitSeg = GetLimit();
            if (limitSeg.IsNotNullOrEmptyOrWhiteSpace()) sb.AppendLine().Append(limitSeg);
            var countSql = string.Empty;
            if (enumSelectToSql == EnumSelectToSql.ToPage)
            {
                var sb2 = new StringBuilder();
                sb2.Append("select count(1)").AppendLine().Append(fromSql);
                if (whereSql.IsNotNullOrEmptyOrWhiteSpace()) sb2.AppendLine().Append(whereSql);
                if (orderSql.IsNotNullOrEmptyOrWhiteSpace()) sb2.AppendLine().Append(orderSql);
                sb2.Append(";");
                countSql = sb2.ToString();
                sb2.Clear();
            }

            sb.Append(';');
            if (countSql.IsNotNullOrEmptyOrWhiteSpace()) sb.AppendLine().Append(countSql);
            var sql = sb.ToString();
            sb.Clear();
            return sql;
        }

        internal string GetSelectSql(LambdaExpression expression, Dictionary<ParameterExpression, object> midValues, List<(string dtoCol, EntityPropertyInfo entPropInfo)> dtoMap = null)
        {
            if (dtoMap != null)
            {
                //entity 映射到 dto
                var cols = dtoMap.OrderBy(i => i.entPropInfo.Order)
                    .Select(i =>
                    {
                        var _val = i.entPropInfo.SelectValueFunc(EntityAliases.First().Alias);
                        if (i.entPropInfo.IsColumnNameSegEqualPropNameSeg)
                            if (_val.EndsWith(i.dtoCol)) return _val;
                        return $"{_val} {i.dtoCol}";
                    })
                    .ToStringSeparated(",");
                return cols;
            }
            else if (expression == null)
            {
                //直接查询所有的列
                var cols = EntityAliases.First().EntityInfo.EntityPropertyInfos
                    .Where(i => i.IsColumn && !i.IsIgnoreSelect)
                    .OrderBy(i => i.Order)
                    .Select(i => i.SelectFunc(EntityAliases.First().Alias))
                    .ToStringSeparated(",");
                return cols;
            }
            else
            {
                //查询指定的列从表达式语句中提取
                Expression exp = expression;
                if (midValues.IsNullOrEmpty())
                    (exp, midValues) = ExpressionHelper.ReduceLambda(expression);
                if (exp is ConstantExpression) throw new Exception($"多余的Select语法(可能使用了ToList(i=>constExp))!");
                var val = BuilderHelper.ParseSql(exp, db, aliasesMap: EntityAliases.Select((i, idx) => new KeyValuePair<ParameterExpression, string>(expression.Parameters[idx], EntityAliases[idx].Alias)).ToList(), midValues: midValues);
                return val;
            }
        }
        internal string GetOrderSql()
        {
            if (this.orderBys.IsNotNullOrEmpty())
            {
                var segs = new List<string>();
                for (int i = 0; i < this.orderBys.Count; i++)
                {
                    var orderBy = this.orderBys[i];
                    if (orderBy.obj is string str)
                    {
                        segs.Add(str);
                        continue;
                    }
                    var descSeg = this.orderBys[i].desc ? " desc" : "";
                    var lambda = orderBy.obj as LambdaExpression;

                    var (exp, midValues) = ExpressionHelper.ReduceLambda(lambda);
                    lambda = exp as LambdaExpression;
                    var aliasesMap = EntityAliases.Select((i, idx) => new KeyValuePair<ParameterExpression, string>(lambda.Parameters[idx], i.Alias));
                    var isNewOrMemberInit = false;
                    var seg = BuilderHelper.ParseSql(exp, db,
                         aliasesMap: aliasesMap,
                         parameters: lambda.Parameters,
                         midValues: midValues,
                         nodeCallBacks:
                         [ new NodeCallBack
                             (
                                exp => exp.NodeType == ExpressionType.New || exp.NodeType == ExpressionType.MemberInit,
                                (exp, next, visit) =>
                                {
                                    isNewOrMemberInit = true;
                                    if(exp.NodeType == ExpressionType.New)
                                    {
                                        var newExp = exp as NewExpression;
                                        var args = newExp.Arguments;
                                        var members = newExp.Members;
                                        var list = new List<string>();
                                        for (int i = 0; i < args.Count; i++)
                                        {
                                            var arg = args[i];
                                            var _val = visit(arg);
                                            list.Add(_val+descSeg);
                                        }
                                        return list.ToStringSeparated(",");
                                    } else{
                                        var initExp = exp as MemberInitExpression;
                                        var bindings = initExp.Bindings;
                                        var list = new List<string>();
                                        for (int i = 0; i < bindings.Count; i++)
                                        {
                                            var binding = bindings[i];
                                            var assign = binding as MemberAssignment;
                                            var exp2 = assign.Expression;
                                            var  _val = visit(exp2);
                                            list.Add(_val+descSeg);
                                        }
                                        return list.ToStringSeparated(",");
                                    }
                                }
                             )
                         ]);
                    if (!isNewOrMemberInit) seg += descSeg;
                    segs.Add(seg);
                }
                return $"order by {segs.ToStringSeparated(",")}";
            }
            return string.Empty;
        }
        internal string GeneFromSql()
        {
            if (fromSql.IsNotNullOrEmptyOrWhiteSpace()) return $"from ({fromSql})";
            if (EntityAliases.Count == 1) return $"from {EntityAliases.First().EntityInfo.TableNameSeg} {EntityAliases.First().Alias}";
            //join
            var sb = new StringBuilder();
            for (int i = 0; i < EntityAliases.Count; i++)
            {
                var entityAlias = EntityAliases[i];
                if (i == 0)
                {
                    sb.Append($"from {entityAlias.EntityInfo.TableNameSeg} {entityAlias.Alias}");
                }
                else
                {
                    var join = FromJoins[i - 1];
                    sb.AppendLine().Append($"    {GetJoinTypeString(join.Type)} {entityAlias.EntityInfo.TableNameSeg} {entityAlias.Alias}");
                    if (join.Expression == null) continue;
                    sb.Append(" on ");
                    //条件
                    var (exp, midValues) = ExpressionHelper.ReduceLambda(join.Expression);
                    var sql = BuilderHelper.ParseSql(exp, db,
                         aliasesMap: join.Expression.Parameters.Select((i, idx) => new KeyValuePair<ParameterExpression, string>(i, EntityAliases[idx].Alias)).ToList(),
                         parameters: join.Expression.Parameters,
                         midValues: midValues);
                    sb.Append(sql);
                }
            }
            return sb.ToString();
        }
        internal string GetJoinTypeString(EnumJoinType joinType)
        {
            switch (joinType)
            {
                case EnumJoinType.LeftJoin:
                    return "left join";
                case EnumJoinType.RightJoin:
                    return "right join";
                case EnumJoinType.InnerJoin:
                    return "inner join";
                case EnumJoinType.CrossJoin:
                    return "cross join";
                default:
                    throw new Exception($"错误的Join类型: {joinType}");
            }
        }
        internal string GeneWhereSql(Dictionary<ParameterExpression, object> midValues = null, IEnumerable<KeyValuePair<ParameterExpression, string>> aliases = null)
        {
            if (Filters.IsNotNullOrEmpty())
            {
                //应用 EntityAliases 上记录的别名
                var newAliases = new List<KeyValuePair<ParameterExpression, string>>();
                if (aliases.IsNotNullOrEmpty()) newAliases.AddRange(aliases);
                foreach (var item in Filters)
                {
                    if (item is LambdaExpression lambda)
                    {
                        if (lambda.Parameters.Count == EntityAliases.Count)
                        {
                            var zips = lambda.Parameters.Zip(EntityAliases);
                            if (zips.All(i => i.First.Type == i.Second.EntityInfo.Type))
                            {
                                zips.ForEach(zip => newAliases.Add(new KeyValuePair<ParameterExpression, string>(zip.First, zip.Second.Alias)));
                            }
                        }
                    }
                }
                var filterSql = DealFilter(Filters, midValues, newAliases);
                return $"where {filterSql}";
            }
            return string.Empty;
        }
        internal string GetLimit()
        {
            var startIndex = this.limitStartIndex >= 0 ? this.limitStartIndex : this.pageSize > 0 ? ((this.pageIndex - 1) * this.pageSize) : -1;
            var pageSize = this.limitStartIndex >= 0 ? this.limitSize : this.pageSize > 0 ? this.pageSize : -1;
            if (startIndex > 0)
            {
                return $"limit {startIndex},{pageSize}";
            }
            else if (startIndex == 0)
            {
                return $"limit {pageSize}";
            }
            return null;
        }
        #endregion

        #region 聚合 & 聚合sql
        #region 聚合Sql
        protected string MaxSql<TKey>(LambdaExpression expression) => ToSql(EnumSelectToSql.Max, expression);
        protected string MinSql<TKey>(LambdaExpression expression) => ToSql(EnumSelectToSql.Min, expression);
        protected string SumSql<TKey>(LambdaExpression expression) => ToSql(EnumSelectToSql.Sum, expression);
        protected string AvgSql<TKey>(LambdaExpression expression) => ToSql(EnumSelectToSql.Avg, expression);
        #endregion

        #region 聚合同步
        protected TKey Max<TKey>(LambdaExpression expression) => db.SelectScalar<TKey>(MaxSql<TKey>(expression));
        protected TKey Min<TKey>(LambdaExpression expression) => db.SelectScalar<TKey>(MinSql<TKey>(expression));
        protected TKey Sum<TKey>(LambdaExpression expression) => db.SelectScalar<TKey>(SumSql<TKey>(expression));
        protected TKey Avg<TKey>(LambdaExpression expression) => db.SelectScalar<TKey>(AvgSql<TKey>(expression));
        #endregion

        #region 聚合异步
        protected async Task<TKey> MaxAsync<TKey>(LambdaExpression expression) => await db.SelectScalarAsync<TKey>(MaxSql<TKey>(expression));
        protected async Task<TKey> MinAsync<TKey>(LambdaExpression expression) => await db.SelectScalarAsync<TKey>(MinSql<TKey>(expression));
        protected async Task<TKey> SumAsync<TKey>(LambdaExpression expression) => await db.SelectScalarAsync<TKey>(SumSql<TKey>(expression));
        protected async Task<TKey> AvgAsync<TKey>(LambdaExpression expression) => await db.SelectScalarAsync<TKey>(AvgSql<TKey>(expression));
        #endregion
        #endregion

        #region 执行
        #region 同步
        protected Page<Dto> SelectPage<Dto>(DBAccess db, string sql)
        {
            var (list, count) = db.SelectMultiple(sql, reader => reader.ReadList<Dto>(), reader => reader.ReadScalar<long>());
            return new Page<Dto>
            {
                List = list,
                TotalCount = count
            };
        }
        protected Dto FirstOrDefault<Dto>(LambdaExpression expression)
        {
            var sql = this.Limit(1).ToSqlList(expression);
            var model = db.SelectModel<Dto>(sql);
            return model;
        }
        protected List<Dto> ToList<Dto>(LambdaExpression expression)
        {
            var sql = ToSql(EnumSelectToSql.ToList, expression);
            var list = db.SelectModelList<Dto>(sql);
            return list;
        }
        protected Page<Dto> ToPage<Dto>(LambdaExpression expression)
        {
            var sql = ToSql(EnumSelectToSql.ToPage, expression);
            return SelectPage<Dto>(db, sql);
        }
        protected Page<Dto> ToPage<Dto>(LambdaExpression expression, int pageIndex, int pageSize)
            => this.Page(pageIndex, pageSize).ToPage<Dto>(expression);
        public long Count()
        {
            var sql = ToSql(EnumSelectToSql.Count, null);
            return db.SelectScalar<long>(sql);
        }
        #endregion

        #region 异步
        protected async Task<Page<Dto>> SelectPageAsync<Dto>(DBAccess db, string sql)
        {
            var (list, count) = await db.SelectMultipleAsync(sql, async reader => await reader.ReadListAsync<Dto>(), async reader => await reader.ReadScalarAsync<long>());
            return new Page<Dto>
            {
                List = list,
                TotalCount = count
            };
        }
        protected async Task<Dto> FirstOrDefaultAsync<Dto>(LambdaExpression expression)
        {
            var sql = this.Limit(1).ToSqlList(expression);
            var model = await db.SelectModelAsync<Dto>(sql);
            return model;
        }
        protected async Task<List<Dto>> ToListAsync<Dto>(LambdaExpression expression)
        {
            var sql = ToSql(EnumSelectToSql.ToList, expression);
            var list = await db.SelectModelListAsync<Dto>(sql);
            return list;
        }
        protected async Task<Page<Dto>> ToPageAsync<Dto>(LambdaExpression expression)
        {
            var sql = ToSql(EnumSelectToSql.ToPage, expression);
            var (list, count) = await db.SelectMultipleAsync(sql, async reader =>
            {
                return await reader.ReadListAsync<Dto>();
            }, async reader => await reader.ReadScalarAsync<long>());
            return new Page<Dto>
            {
                List = list,
                TotalCount = count
            };
        }
        protected async Task<Page<Dto>> ToPageAsync<Dto>(LambdaExpression expression, int pageIndex, int pageSize)
            => await this.Page(pageIndex, pageSize).ToPageAsync<Dto>(expression);
        public async Task<long> CountAsync()
        {
            var sql = ToSql(EnumSelectToSql.Count, null);
            return await db.SelectScalarAsync<long>(sql);
        }
        #endregion
        #endregion        
    }
    public class GroupBuilderBase
    {
        protected LambdaExpression groupExpression = null;
        protected readonly SelectBuilderBase selectBuilder = null;
        protected readonly DBAccess db = null;
        protected List<object> havings = [];
        internal GroupBuilderBase(SelectBuilderBase selectBuilder, LambdaExpression groupExpression)
        {
            this.groupExpression = groupExpression;
            this.selectBuilder = selectBuilder;
            this.db = selectBuilder.db;
        }

        #region 分页
        private int? pageIndex = null;
        private int? pageSize = null;
        private int? limitSize = null;
        private int? limitStartIndex = null;
        public virtual GroupBuilderBase Page(int pageIndex, int pageSize)
        {
            Ensure.That(pageIndex >= 1, $"{nameof(pageIndex)}必须从1开始!");
            Ensure.That(pageSize > 0, $"{nameof(pageSize)}必须大于0!");
            this.pageIndex = pageIndex;
            this.pageSize = pageSize;
            this.limitSize = this.limitStartIndex = null;
            return this;
        }

        private GroupBuilderBase limit(int size, int startIndex)
        {
            Ensure.That(startIndex >= 0, $"{nameof(startIndex)}必须大于等于0!");
            Ensure.That(size >= 0, $"{nameof(size)}必须大于等于0!");
            this.limitSize = size;
            this.limitStartIndex = startIndex;
            this.pageIndex = this.pageSize = null;
            return this;
        }
        public virtual GroupBuilderBase Limit(int size) => this.limit(size, 0);
        public virtual GroupBuilderBase Limit(int startIndex, int size) => this.limit(size, startIndex);
        #endregion

        #region 排序
        private List<(object obj, bool desc)> orderBys = [];
        protected virtual GroupBuilderBase OrderBy(LambdaExpression expression)
        {
            orderBys.Add((expression, false));
            return this;
        }

        protected virtual GroupBuilderBase OrderByDesc(LambdaExpression expression)
        {
            orderBys.Add((expression, true));
            return this;
        }

        /// <summary>
        /// 排序,如: select.Order("age desc,id")
        /// </summary>
        public virtual GroupBuilderBase Order(string orderSeg)
        {
            orderBys.Add((orderSeg, false));
            return this;
        }
        #endregion

        #region ToSql
        protected string ToSql(EnumSelectToSql enumSelectToSql, LambdaExpression expression)
        {
            /*
            select t.clsid ,count(1) 
            from testtbl t 
            where t.id>100
            group by t.clsid 
            having count(1)>1
            order by count(1)
            limit 10
            */
            var sb = new StringBuilder();
            var alias = selectBuilder.EntityAliases;
            var aliasMap = alias.Select((alias, idx) => new KeyValuePair<ParameterExpression, string>(groupExpression.Parameters[idx], alias.Alias));

            //group by
            var groupBySeg = "";
            var dic = new Dictionary<string, string>();
            if (groupExpression.Body.NodeType == ExpressionType.MemberAccess)
            {
                //GroupBy(i=>i.Tyle)
                var member = groupExpression.Body as MemberExpression;
                var seg = BuilderHelper.ParseSql(member, db, aliasMap, groupExpression.Parameters);
                groupBySeg = seg;
                dic.Add("DBUtil.Builders.IGroupFilter.Key", seg);
            }
            else if (groupExpression.Body.NodeType == ExpressionType.New)
            {
                //GroupBy(i=>new{i.Name,i.Age}
                var newExp = groupExpression.Body as NewExpression;
                var args = newExp.Arguments;
                var members = newExp.Members;
                var list = new List<string>();
                for (int i = 0; i < args.Count; i++)
                {
                    var arg = args[i];
                    //expr中的这个属性可能是常量
                    var _val = BuilderHelper.ParseSql(arg, db, aliasMap, groupExpression.Parameters);
                    list.Add(_val);
                    dic.Add("DBUtil.Builders.IGroupFilter.Key." + members[i].Name, _val);
                }
                groupBySeg = list.ToStringSeparated(",");
            }

            //select
            switch (enumSelectToSql)
            {
                case EnumSelectToSql.Count:
                    sb.Append("select count(1)");
                    break;
                case EnumSelectToSql.ToList:
                case EnumSelectToSql.ToPage:
                    var val = BuilderHelper.ParseSql(expression, db, aliasMap, null, dic);
                    sb.Append("select ").Append(val);
                    break;
            }

            //from
            var fromSql = selectBuilder.GeneFromSql();
            sb.AppendLine().Append(fromSql);
            //where 过滤
            var whereSql = selectBuilder.GeneWhereSql();
            if (whereSql.IsNotNullOrEmptyOrWhiteSpace()) sb.AppendLine().Append(whereSql);
            //group by
            sb.AppendLine().Append("group by ").Append(groupBySeg);
            //having 过滤
            var havingSql = GetHavingSql(aliasMap, dic);
            if (havingSql.IsNotNullOrEmptyOrWhiteSpace()) sb.AppendLine().Append(havingSql);
            //order by
            var orderSql = GetOrderSql(aliasMap, dic);
            if (orderSql.IsNotNullOrEmptyOrWhiteSpace()) sb.AppendLine().Append(orderSql);

            //limit
            var limitSeg = GetLimit();
            if (limitSeg.IsNotNullOrEmptyOrWhiteSpace()) sb.AppendLine().Append(limitSeg);
            var countSql = string.Empty;
            if (enumSelectToSql == EnumSelectToSql.ToPage)
            {
                var sb2 = new StringBuilder();
                sb2.Append("select count(1)").AppendLine().Append(fromSql);
                if (whereSql.IsNotNullOrEmptyOrWhiteSpace()) sb2.AppendLine().Append(whereSql);
                sb2.AppendLine().Append("group by ").Append(groupBySeg);
                if (havingSql.IsNotNullOrEmptyOrWhiteSpace()) sb2.AppendLine().Append(havingSql);
                if (orderSql.IsNotNullOrEmptyOrWhiteSpace()) sb2.AppendLine().Append(orderSql);
                sb2.Append(';');
                countSql = sb2.ToString();
                sb2.Clear();
            }

            sb.Append(';');
            if (countSql.IsNotNullOrEmptyOrWhiteSpace()) sb.AppendLine().Append(countSql);
            var sql = sb.ToString();
            sb.Clear();
            return sql;
        }
        internal string GetLimit()
        {
            var startIndex = this.limitStartIndex >= 0 ? this.limitStartIndex : this.pageSize > 0 ? ((this.pageIndex - 1) * this.pageSize) : -1;
            var pageSize = this.limitStartIndex >= 0 ? this.limitSize : this.pageSize > 0 ? this.pageSize : -1;
            if (startIndex > 0)
            {
                return $"limit {startIndex},{pageSize}";
            }
            else if (startIndex == 0)
            {
                return $"limit {pageSize}";
            }
            return null;
        }
        internal string GetOrderSql(IEnumerable<KeyValuePair<ParameterExpression, string>> aliasMap, Dictionary<string, string> dic)
        {
            if (this.orderBys.IsNotNullOrEmpty())
            {
                var segs = new List<string>();
                for (int i = 0; i < this.orderBys.Count; i++)
                {
                    var orderBy = this.orderBys[i];
                    if (orderBy.obj is string str)
                    {
                        segs.Add(str);
                        continue;
                    }
                    var isDesc = this.orderBys[i].desc;
                    var lambda = orderBy.obj as LambdaExpression;
                    var orderSql = BuilderHelper.ParseSql(lambda, db, aliasMap, null, dic);
                    segs.Add(orderSql);
                }
                return $"order by {segs.ToStringSeparated(",")}";
            }
            return string.Empty;
        }
        internal string GetHavingSql(IEnumerable<KeyValuePair<ParameterExpression, string>> aliasMap, Dictionary<string, string> dic)
        {
            var filterSqls = new List<string>();
            foreach (var filter in havings)
            {
                if (filter is string str && str.IsNotNullOrEmptyOrWhiteSpace())
                {
                    filterSqls.Add(str);
                    continue;
                }
                if (filter is not LambdaExpression lambda) throw new Exception($"必须为LambdaExpression!");
                var (exp, midValues) = ExpressionHelper.ReduceLambda(lambda);
                if (exp.NodeType == ExpressionType.Constant)
                {
                    var b = (exp as ConstantExpression).Value.To<bool>();
                    if (b) filterSqls.Add("1=1");
                    else filterSqls.Add("1=0");
                }
                else if (exp is ParameterExpression parameter && midValues.TryGetValue(parameter, out object _val))
                {
                    var b = _val.To<bool>();
                    if (b) filterSqls.Add("1=1");
                    else filterSqls.Add("1=0");
                }
                else
                {
                    lambda = exp as LambdaExpression;
                    string tmp = string.Empty;

                    tmp = BuilderHelper.ParseSql(lambda, db, aliasMap, null, dic, midValues: midValues);
                    if (tmp.IsNotNullOrEmptyOrWhiteSpace()) filterSqls.Add(tmp);
                }
            }

            if (filterSqls.IsNotNullOrEmpty())
            {
                if (filterSqls.Count == 1) return $"having {filterSqls[0]}";
                else return $"having {filterSqls.Select(i => $"({i})").ToStringSeparated(" and ")}";
            }
            return string.Empty;

        }

        protected string ToSqlFirstOrDefault(LambdaExpression expression)
            => Limit(1).ToSql(EnumSelectToSql.ToList, expression);
        protected string ToSqlList(LambdaExpression expression)
            => ToSql(EnumSelectToSql.ToList, expression);
        protected string ToSqlPage(LambdaExpression expression)
            => ToSql(EnumSelectToSql.ToPage, expression);
        protected string ToSqlPage(LambdaExpression expression, int pageIndex, int pageSize)
            => Page(pageIndex, pageSize).ToSql(EnumSelectToSql.ToPage, expression);
        public virtual string ToSqlCount()
            => ToSql(EnumSelectToSql.Count, null);
        #endregion

        #region 执行
        #region 同步
        protected Dto FirstOrDefault<Dto>(LambdaExpression expression)
        {
            Ensure.NotNull(expression, nameof(expression));
            var sql = this.Limit(1).ToSqlList(expression);
            var model = db.SelectModel<Dto>(sql);
            return model;
        }
        protected List<Dto> ToList<Dto>(LambdaExpression expression)
        {
            Ensure.NotNull(expression, nameof(expression));
            var sql = this.ToSqlList(expression);
            var model = db.SelectModelList<Dto>(sql);
            return model;
        }
        protected Page<Dto> ToPage<Dto>(LambdaExpression expression)
        {
            var sql = ToSql(EnumSelectToSql.ToPage, expression);
            var (list, count) = db.SelectMultiple(sql, reader =>
            {
                return reader.ReadList<Dto>();
            }, reader => reader.ReadScalar<long>());
            return new Page<Dto>
            {
                List = list,
                TotalCount = count
            };
        }
        protected Page<Dto> ToPage<Dto>(LambdaExpression expression, int pageIndex, int pageSize)
        {
            return this.Page(pageIndex, pageSize).ToPage<Dto>(expression);
        }
        public long Count()
        {
            var sql = ToSql(EnumSelectToSql.Count, null);
            return db.SelectScalar<long>(sql);
        }
        #endregion
        #region 异步
        protected async Task<Dto> FirstOrDefaultAsnc<Dto>(LambdaExpression expression)
        {
            Ensure.NotNull(expression, nameof(expression));
            var sql = this.Limit(1).ToSqlList(expression);
            var model = await db.SelectModelAsync<Dto>(sql);
            return model;
        }
        protected async Task<List<Dto>> ToListAsync<Dto>(LambdaExpression expression)
        {
            Ensure.NotNull(expression, nameof(expression));
            var sql = this.ToSqlList(expression);
            var model = await db.SelectModelListAsync<Dto>(sql);
            return model;
        }
        protected async Task<Page<Dto>> ToPageAsync<Dto>(LambdaExpression expression)
        {
            var sql = ToSql(EnumSelectToSql.ToPage, expression);
            var (list, count) = await db.SelectMultipleAsync(sql, async reader =>
            {
                //todo: 使用特定的匹配规则 而不是先到 entity 再转dto 也不是直接读取到dto
                return await reader.ReadListAsync<Dto>();
            }, async reader => await reader.ReadScalarAsync<long>());
            return new Page<Dto>
            {
                List = list,
                TotalCount = count
            };
        }
        protected async Task<Page<Dto>> ToPageAsync<Dto>(LambdaExpression expression, int pageIndex, int pageSize)
        {
            return await this.Page(pageIndex, pageSize).ToPageAsync<Dto>(expression);
        }
        public async Task<long> CountAsync()
        {
            var sql = ToSql(EnumSelectToSql.Count, null);
            return await db.SelectScalarAsync<long>(sql);
        }
        #endregion
        #endregion
    }
}